import psycopg2
from datetime import datetime

DB_CONFIG = {
    "dbname": "game_trading_v2",
    "user": "postgres",
    "password": "145114",
    "host": "localhost",
    "port": "5432"
}

def get_conn():
    return psycopg2.connect(**DB_CONFIG)

def check_login(username, password):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT UserID, Role FROM Users WHERE Username=%s AND Password=%s", (username, password))
    result = cur.fetchone()
    cur.close()
    conn.close()
    return result  # (UserID, Role) 或 None

def register_user(username, password, email):
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("SELECT 1 FROM Users WHERE Username=%s OR Email=%s", (username, email))
        if cur.fetchone():
            return None
        cur.execute(
            "INSERT INTO Users (Username, Password, Email, RegistrationDate, Role) VALUES (%s, %s, %s, %s, %s) RETURNING UserID",
            (username, password, email, datetime.now(), 'user')
        )
        user_id = cur.fetchone()[0]
        conn.commit()
        return user_id
    except Exception as e:
        print("注册异常：", e)
        conn.rollback()
        return None
    finally:
        cur.close()
        conn.close()

def get_user_info(user_id):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT UserID, Username, Email FROM Users WHERE UserID=%s", (user_id,))
    result = cur.fetchone()
    cur.close()
    conn.close()
    return result

def update_password(user_id, new_password):
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("UPDATE Users SET Password=%s WHERE UserID=%s", (new_password, user_id))
        conn.commit()
        return True
    except Exception as e:
        print("修改密码异常：", e)
        conn.rollback()
        return False
    finally:
        cur.close()
        conn.close()

def get_on_sale_equipments():
    """获取所有在售装备 - 使用ActiveEquipmentView"""
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("""
            SELECT EquipmentID, EquipmentName, Category, Level, Rarity, Price, ListedDate, 'active' as Status
            FROM ActiveEquipmentView
            ORDER BY ListedDate DESC
        """)
        return cur.fetchall()
    except Exception as e:
        print(f"获取在售装备错误: {e}")
        return []
    finally:
        cur.close()
        conn.close()

def get_my_orders(user_id):
    """获取用户的订单 - 使用UserTransactionsView"""
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("""
            SELECT TransactionID, EquipmentID, SellerID, BuyerID, Price, 'completed' as Status, TransactionDate
            FROM UserTransactionsView
            WHERE BuyerID=%s OR SellerID=%s
            ORDER BY TransactionDate DESC
            LIMIT 100
        """, (user_id, user_id))
        return cur.fetchall()
    except Exception as e:
        print(f"获取订单错误: {e}")
        return []
    finally:
        cur.close()
        conn.close()

def get_my_equipments(user_id):
    """获取用户发布的装备 - 使用UserEquipmentView"""
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("""
            SELECT EquipmentID, EquipmentName, Price, ListedDate, Status
            FROM UserEquipmentView
            WHERE UserID=%s
            ORDER BY ListedDate DESC
            LIMIT 100
        """, (user_id,))
        return cur.fetchall()
    except Exception as e:
        print(f"获取用户装备错误: {e}")
        return []
    finally:
        cur.close()
        conn.close()

def get_wallet_info(user_id):
    """获取用户钱包余额 - 使用UserWalletSummary"""
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("SELECT Balance FROM UserWalletSummary WHERE UserID=%s", (user_id,))
        result = cur.fetchone()
        return result[0] if result else 0
    except Exception as e:
        print(f"获取钱包信息错误: {e}")
        return 0
    finally:
        cur.close()
        conn.close()

def get_wallet_records(user_id):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("""
        SELECT RecordID, Type, Amount, Timestamp
        FROM WalletRecords
        WHERE UserID=%s
        ORDER BY Timestamp DESC
        LIMIT 100
    """, (user_id,))
    result = cur.fetchall()
    cur.close()
    conn.close()
    return result

def get_price_change_messages(user_id):
    """
    获取用户的价格变动消息
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("""
            SELECT m.MessageID, m.EquipmentID, e.EquipmentName, 
                   m.OldPrice, m.NewPrice, m.ChangeDate, m.IsRead
            FROM PriceChangeMessages m
            JOIN Equipments e ON m.EquipmentID = e.EquipmentID
            WHERE m.UserID = %s
            ORDER BY m.ChangeDate DESC
        """, (user_id,))
        messages = []
        for row in cur.fetchall():
            messages.append({
                "MessageID": row[0],
                "EquipmentID": row[1],
                "EquipmentName": row[2],
                "OldPrice": row[3],
                "NewPrice": row[4],
                "ChangeDate": row[5],
                "IsRead": row[6]
            })
        return messages
    except Exception as e:
        print(f"获取价格变动消息错误: {e}")
        return []
    finally:
        cur.close()
        conn.close()

def mark_message_read(message_id):
    """
    将消息标记为已读
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("UPDATE PriceChangeMessages SET IsRead = TRUE WHERE MessageID = %s", (message_id,))
        conn.commit()
        return True
    except Exception as e:
        conn.rollback()
        print(f"标记消息已读错误: {e}")
        return False
    finally:
        cur.close()
        conn.close()

def update_equipment_price(equipment_id, new_price):
    """
    更新装备价格并创建价格变动消息
    
    注意：这里不需要手动创建价格变动消息，因为我们有一个触发器
    equipment_price_change_trigger会自动处理
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        # 获取当前价格
        cur.execute("SELECT Price FROM Equipments WHERE EquipmentID = %s", (equipment_id,))
        result = cur.fetchone()
        if not result:
            return False, "装备不存在"
        
        # 更新价格 - 触发器会自动处理通知
        cur.execute("UPDATE Equipments SET Price = %s WHERE EquipmentID = %s", (new_price, equipment_id))
        
        conn.commit()
        return True, "价格已更新，触发器已自动通知相关用户"
    except Exception as e:
        conn.rollback()
        print(f"更新价格错误: {e}")
        return False, f"更新价格失败: {e}"
    finally:
        cur.close()
        conn.close()

def add_equipment(seller_id, equipment_name, category, level, rarity, attributes, price, image_url=None):
    """
    添加新装备
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("""
            INSERT INTO Equipments 
            (SellerID, EquipmentName, Category, Level, Rarity, Attributes, Price, ListedDate, Status, ImageURL)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'active', %s)
            RETURNING EquipmentID
        """, (seller_id, equipment_name, category, level, rarity, attributes, price, datetime.now(), image_url))
        
        equipment_id = cur.fetchone()[0]
        conn.commit()
        return equipment_id
    except Exception as e:
        conn.rollback()
        print(f"添加装备错误: {e}")
        return None
    finally:
        cur.close()
        conn.close()

def remove_equipment(equipment_id, seller_id):
    """
    下架装备（将状态改为removed）
    
    注意：不需要手动从购物车中删除，触发器equipment_inactive_trigger会自动处理
    """
    conn = get_conn()
    cur = conn.cursor() # 游标
    try:
        # 检查是否为装备所有者
        cur.execute("SELECT 1 FROM Equipments WHERE EquipmentID = %s AND SellerID = %s", (equipment_id, seller_id))
        if not cur.fetchone():
            return False, "您不是该装备的所有者或装备不存在"
        
        # 更新装备状态 - 触发器会自动从购物车中删除
        cur.execute("UPDATE Equipments SET Status = 'inactive' WHERE EquipmentID = %s", (equipment_id,))
        
        conn.commit()
        return True, "装备已成功下架，触发器已自动从购物车中移除"
    except Exception as e:
        conn.rollback()
        print(f"下架装备错误: {e}")
        return False, f"下架装备失败: {e}"
    finally:
        cur.close()
        conn.close()